package ac.kr.ml.dao;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import kr.ac.ml.mvc.FriendList;
import ac.kr.ml.vo.ContentVo;

public class ListDao {

	// Friend 占쏙옙占싱� 친占쏙옙 占쏙옙占쏙옙 占쌍깍옙
	public void insertFriend(ArrayList<FriendList> list) {
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");
			stmt = conn.createStatement();
			int length = list.size();
			for (int i = 0; i < length; ++i) {
				String sql = "insert into Friend (empID, fmpID) values ('"
						+ list.get(i).getempID() + "','"
						+ list.get(i).getfmpID() + "')";
				stmt.executeUpdate(sql);
			}
		}

		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				stmt.close();

			} catch (SQLException e) {
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}

	}

	// 부서목록
	public List<ContentVo> getDeptList(String str) {
		List<ContentVo> list = new ArrayList<ContentVo>();

		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "select " + str + " from department";

			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				ContentVo vo = new ContentVo();
				vo.setdeptName(rs.getString("department_name"));
				list.add(vo);
			}

			rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally // 예외가 발생하거나 안해도 처리해야될 내용
		{// stmt를 닫는것
			try {
				stmt.close();
			} catch (SQLException e) {
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
		return list;
	}

	public void insert(ContentVo vo) {

		Connection conn = null;
		Statement stmt = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			// String dburl =
			// "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			// conn = DriverManager.getConnection(dburl, "freshman",
			// "freshman!$");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "insert into CONTENT (content, time, good, bad, reply_cont) values ('"
					+ vo.getContent()
					+ "', '"
					+ vo.getTime()
					+ "', '"
					+ vo.getGood()
					+ "', '"
					+ vo.getBad()
					+ "', '"
					+ vo.getReply_cont() + "')";

			// ResultSet rs = stmt.executeQuery(sql);

			stmt.executeUpdate(sql);

			// rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				stmt.close();

			} catch (SQLException e) {
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	// 占쏙옙호占쏙옙占쏙옙
	public boolean confirm(String str) {
		boolean confirm = false;
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "select empID from addressinfo where hp ='" + str
					+ "'";
			ResultSet rs = stmt.executeQuery(sql);
			int empID = 0;

			while (rs.next()) {
				ContentVo vo = new ContentVo();
				vo.setempID(rs.getInt(1));
				empID = vo.getempID();
				if (empID > 0) {
					confirm = true;
				} else
					confirm = false;
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			confirm = false;

		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {

			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
		return confirm;
	}

	public List<ContentVo> getAddressList() {
		List<ContentVo> list = new ArrayList<ContentVo>();

		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "select * from addressinfo order by posID asc";

			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				ContentVo vo = new ContentVo();
				vo.setempID(rs.getInt(1));
				vo.setdeptID(rs.getInt(4));
				vo.setdeptName(rs.getString(5));
				vo.setposName(rs.getString(7));
				vo.setuserName(rs.getString(13));
				vo.setemail(rs.getString(14));
				vo.sethp(rs.getString(15));
				vo.setempCode(rs.getString("empCode"));
				list.add(vo);
			}

			rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
		return list;
	}

}
